import xlrd
from bs4 import BeautifulSoup
import sqlite3


def read_excel_data():
    conn = sqlite3.connect('stock.db')
    cursor = conn.cursor()
    with open("./category.xls",encoding='utf-8') as f:
        htmlfile = f.read()
        soup = BeautifulSoup(htmlfile,'lxml')
        tables = soup.findAll('table')
        tab = tables[0]
        for tr in tab.findAll('tr')[1:]:
            tds =  tr.findAll('td')
            long_cate_name = tds[4].getText().strip()
            stock_code = tds[0].getText().strip()
            stock_name = tds[1].getText().strip()
            cat_id = get_category_id(long_cate_name,cursor,conn)
            if cat_id is not None:
                stock_id = insert_stock_data(stock_code,stock_name,cat_id,cursor,conn)
                print(cat_id,stock_id)
    cursor.close()
    conn.close()

def get_category_id(long_cate_name,cursor,conn):
    cate_name_arr = long_cate_name.split("-")
    cursor.execute("select * from category where long_cate_name=?",(long_cate_name,))
    cat3 = cursor.fetchone()
    if cat3 is None and len(cate_name_arr) >= 3:
        cursor.execute("select * from category where cate_name=? and parent_id =0",(cate_name_arr[0],))
        cat1 = cursor.fetchone()
        if cat1 is None:
            cursor.execute("insert into category(cate_name,long_cate_name,parent_id) values(?,?,?);",(cate_name_arr[0],cate_name_arr[0],0))
            conn.commit()
            cat1_id = cursor.lastrowid
        else:
            cat1_id = cat1[0]
        long_cate_name2 = "-".join(cate_name_arr[:2])
        cursor.execute("select * from category where cate_name=? and parent_id =?",(cate_name_arr[1],cat1_id))
        cat2 = cursor.fetchone()
        if cat2 is None:
            cursor.execute("insert into category(cate_name,long_cate_name,parent_id) values(?,?,?);",(cate_name_arr[1],long_cate_name2,cat1_id))
            conn.commit()
            cat2_id = cursor.lastrowid
        else:
            cat2_id = cat2[0]
        cursor.execute("insert into category(cate_name,long_cate_name,parent_id) values(?,?,?);",(cate_name_arr[2],long_cate_name,cat2_id))
        conn.commit()
        cat3_id = cursor.lastrowid
    elif cat3 is not None:
        cat3_id = cat3[0]
    else:
        print("{0} is invalid".format(long_cate_name))
        cat3_id = None
    return cat3_id
def insert_stock_data(stock_code,stock_name,category_id,cursor,conn):
    cursor.execute("select * from stock where stock_code=?",(stock_code,))
    stock_info = cursor.fetchone()
    if stock_info is None:
        cursor.execute("insert into stock(stock_code,stock_name,category_id) values(?,?,?);",(stock_code,stock_name,category_id))
        conn.commit()
    else:
        cursor.execute("update stock set stock_name=?,category_id=? where stock_code=?;",(stock_name,category_id,stock_code))
        conn.commit()
    return cursor.lastrowid
        

def create_table():
    conn = sqlite3.connect('stock.db')
    cursor = conn.cursor()
    cursor.execute('create table IF NOT EXISTS category  (id INTEGER PRIMARY KEY AUTOINCREMENT, cate_name varchar(50),long_cate_name varchar(150),parent_id INTEGER)')
    cursor.execute('create table IF NOT EXISTS stock  (id INTEGER PRIMARY KEY AUTOINCREMENT, stock_code varchar(50),stock_name varchar(50),category_id INTEGER,UNIQUE（stock_code）ON CONFLICT REPLACE)')
    conn.commit()
    cursor.close()
    conn.close()
    
        

        
        
    
    

if __name__ == "__main__":
    """创建表格如果存在则不创建"""
    create_table()
    """从Excel读取数据插入表格"""
    read_excel_data()
    
